// HSS.Data.DataAccess.cs
// ----------------------------------------------------------------------------
// Licensed under the MIT license
// http://www.opensource.org/licenses/mit-license.html
// ----------------------------------------------------------------------------
// HighSpeed-Solutions, LLC
// Copyright (c) 2001-2010
// ----------------------------------------------------------------------------
// File:       DataAccess.cs
// Author:     HSS\gbanta
// Created:    08/12/2010
// Modified:   12/04/2010
// ----------------------------------------------------------------------------
namespace HSS.Data
{
	#region Using Directives
	using System;
	using System.Collections.Generic;
	using System.Data;
	using System.Data.SqlClient;
	#endregion

	#region DataAccess
	/// <summary>
	/// A lightweight wrapper for accessing an MS Sql Database using the .NET SqlClient.
	/// </summary>
	public sealed class DataAccess : IDisposable
	{
		#region Fields
		/// <summary>
		/// Private SqlConnection Object to execute the (SqlCommand)SqlCmd against
		/// </summary>
		private SqlConnection sdrConn;
		/// <summary>
		/// Private SqlCommand Object to execute against
		/// </summary>
		private SqlCommand SqlCmd;
		bool disposed = false;
		#endregion

		#region Constructor
		/// <summary>
		/// Constructor. Creates a new uninitialized SqlCommand object.
		/// </summary>
		/// <param name="connection">The <see cref="DataAccessConnection"/>.</param>
		public DataAccess(DataAccessConnection connection)
		{
			this.Connection = connection;
			this.SqlCmd = new SqlCommand();
			this.SqlCmd.CommandTimeout = connection.CommandTimeout;
		}
		/// <summary>
		/// Constructor. Creates a new uninitialized SqlCommand object.
		/// </summary>
		/// <param name="connectionString">The ConnectionString</param>
		public DataAccess(string connectionString)
		{
			this.Connection = new DataAccessConnection(connectionString);
			this.Connection.CommandTimeout = 60;
			this.SqlCmd = new SqlCommand();
			this.SqlCmd.CommandTimeout = 60;
		}
		/// <summary>
		/// Constructor. Creates a new uninitialized SqlCommand object.
		/// </summary>
		/// <param name="connectionString">The ConnectionString</param>
		/// <param name="commandTimeout">The Timeout for the Command object</param>
		public DataAccess(string connectionString, int commandTimeout)
		{
			this.Connection = new DataAccessConnection(connectionString);
			this.Connection.CommandTimeout = commandTimeout;
			this.SqlCmd = new SqlCommand();
			this.SqlCmd.CommandTimeout = commandTimeout;
		}
		#endregion

		#region Properties
		/// <summary>
		/// Gets the current <see cref="DataAccessConnection"/>.
		/// </summary>
		public DataAccessConnection Connection
		{
			get;
			private set;
		}
		/// <summary>
		/// Gets the current connection string
		/// </summary>
		public string ConnectionString
		{
			get
			{
				if (null == this.Connection)
					return string.Empty;
				return this.Connection.ConnectionString;
			}
		}
		/// <summary>
		/// The OutParameters from a SqlDatabase
		/// </summary>
		public List<SqlParameter> SqlOutParameters
		{
			get
			{
				if (null == _sqlOutParameters)
					_sqlOutParameters = new List<SqlParameter>();
				return _sqlOutParameters;
			}
		} List<SqlParameter> _sqlOutParameters;
		#endregion

		#region  Methods

		/// <summary>
		/// Executes the requested stored procedure returning an <see cref="SqlDataReader"/> using the provided <see cref="CommandBehavior"/>.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		/// <param name="behavior">Specify the CommandBehavior</param>
		/// <param name="cmd">The SqlCommand OUT parameter.</param>
		/// <returns>An instantiated SQLDataReader</returns>
		public SqlDataReader ExecSqlDataReader(string procedureName, CommandBehavior behavior, out SqlCommand cmd)
		{
			AddReturnValue();
			SqlDataReader sdr = null;

			if (null != this.sdrConn)
			{
				if (this.sdrConn.State == ConnectionState.Open)
					this.sdrConn.Close();
				this.sdrConn.ConnectionString = this.GetConnectionString();
			}
			else
				this.sdrConn = new SqlConnection(this.GetConnectionString());

			this.sdrConn.Open();
			cmd = this.sdrConn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = procedureName;
			var prms = new SqlParameter[this.SqlCmd.Parameters.Count];
			this.SqlCmd.Parameters.CopyTo(prms, 0);
			this.SqlCmd.Parameters.Clear();
			cmd.Parameters.AddRange(prms);
			sdr = cmd.ExecuteReader(behavior);

			return sdr;
		}
		/// <summary>
		/// Executes the requested stored procedure returning an <see cref="SqlDataReader"/> with a <see cref="CommandBehavior"/> of CloseConnection.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		/// <param name="cmd">The SqlCommand OUT parameter.</param>
		/// <returns>An executed SqlDataReader.</returns>
		public SqlDataReader ExecSqlDataReader(string procedureName, out SqlCommand cmd)
		{
			return this.ExecSqlDataReader(procedureName, CommandBehavior.CloseConnection, out cmd);
		}
		/// <summary>
		/// Executes the requested stored procedure returning an <see cref="SqlDataReader"/> using the provided <see cref="CommandBehavior"/>.
		/// </summary>
		/// <param name="procedureName">Stored Procedure Name</param>
		/// <param name="behavior">Specify the CommandBehavior</param>
		/// <returns>An instantiated SqlDataReader</returns>
		public SqlDataReader ExecSqlDataReader(string procedureName, CommandBehavior behavior)
		{
			SqlDataReader sdr = null;

			if (null != this.sdrConn)
			{
				if (this.sdrConn.State == ConnectionState.Open)
					this.sdrConn.Close();
				this.sdrConn.ConnectionString = this.GetConnectionString();
			}
			else
				this.sdrConn = new SqlConnection(this.GetConnectionString());

			this.sdrConn.Open();
			var cmd = this.sdrConn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = procedureName;
			var prms = new SqlParameter[this.SqlCmd.Parameters.Count];
			this.SqlCmd.Parameters.CopyTo(prms, 0);
			this.SqlCmd.Parameters.Clear();
			cmd.Parameters.AddRange(prms);
			sdr = cmd.ExecuteReader(behavior);
			return sdr;
		}
		/// <summary>
		/// Executes the requested stored procedure returning an <see cref="SqlDataReader"/> with a <see cref="CommandBehavior"/> of CloseConnection.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		/// <returns>An instantiated SQLDataReader</returns>
		public SqlDataReader ExecSqlDataReader(string procedureName)
		{
			return ExecSqlDataReader(procedureName, CommandBehavior.CloseConnection);
		}
		/// <summary>
		/// Executes the requested SQL Statement.
		/// </summary>
		/// <param name="SQLStatement">The SQL Statement to execute</param>
		/// <returns>An instantiated SqlDataReader containing the results</returns>
		public SqlDataReader ExecSqlDataReaderSQL(string SQLStatement)
		{
			if (null != sdrConn)
			{
				if (sdrConn.State == ConnectionState.Open)
					sdrConn.Close();
				sdrConn.ConnectionString = this.GetConnectionString();
			}
			else
				sdrConn = new SqlConnection(this.GetConnectionString());

			SqlDataReader sdr = null;
			try
			{
				sdrConn.Open();
				SqlCmd.CommandType = CommandType.Text;
				SqlCmd.CommandText = SQLStatement;
				SqlCmd.Connection = sdrConn;
				sdr = SqlCmd.ExecuteReader();
			}
			finally
			{
				SqlCmd.Parameters.Clear();
			}
			return sdr;
		}

		/// <summary>
		/// Executes the requested stored procedure returning a DataRowReader.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		/// <returns>An instantiated DataRowReader</returns>
		public DataRowReader ExecDataRowReader(string procedureName)
		{
			DataRowReader ddr = null;
			using (var sqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					sqlConn.Open();
					this.SqlCmd.CommandType = CommandType.StoredProcedure;
					this.SqlCmd.CommandText = procedureName;
					this.SqlCmd.Connection = sqlConn;
					ddr = new DataRowReader(this.SqlCmd.ExecuteReader());
					this.GetOutParameters();
				}
				finally
				{
					this.SqlCmd.Parameters.Clear();
				}
			}
			return ddr;
		}
		/// <summary>
		/// Executes the requested stored procedure returning a DataRowReader.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		/// <param name="spReturnValue">The return value from the stored procedure</param>
		/// <returns>An instantiated DataRowReader</returns>
		public DataRowReader ExecDataRowReader(string procedureName, ref int spReturnValue)
		{
			DataRowReader ddr = null;
			AddReturnValue();
			using (var sqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					sqlConn.Open();
					this.SqlCmd.CommandType = CommandType.StoredProcedure;
					this.SqlCmd.CommandText = procedureName;
					this.SqlCmd.Connection = sqlConn;
					ddr = new DataRowReader(this.SqlCmd.ExecuteReader());
					spReturnValue = GetReturnValue();
					this.GetOutParameters();
				}
				finally
				{
					this.SqlCmd.Parameters.Clear();
				}
			}
			return ddr;
		}
		/// <summary>
		/// Executes the requested SQL Statement.
		/// </summary>
		/// <param name="SQLStatement">The SQL Statement to execute</param>
		/// <returns>An instantiated DataRowReader</returns>
		public DataRowReader ExecDataRowReaderSQL(string SQLStatement)
		{
			using (var sqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					sqlConn.Open();
					this.SqlCmd.CommandType = CommandType.Text;
					this.SqlCmd.CommandText = SQLStatement;
					this.SqlCmd.Connection = sqlConn;
					return new DataRowReader(this.SqlCmd.ExecuteReader());
				}
				finally
				{
					this.SqlCmd.Parameters.Clear();
				}
			}
		}

		/// <summary>
		/// Executes the requested stored procedure returning a DataSet.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		/// <returns>DataSet</returns>
		public DataSet ExecDataSet(string procedureName)
		{
			SqlDataAdapter adapter = new SqlDataAdapter();
			DataSet dataSet = new DataSet();
			dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture;

			using (SqlConnection SqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					SqlConn.Open();
					SqlCmd.CommandType = CommandType.StoredProcedure;
					SqlCmd.CommandText = procedureName;
					SqlCmd.Connection = SqlConn;
					adapter.SelectCommand = SqlCmd;
					adapter.Fill(dataSet);
					GetOutParameters();
				}
				finally
				{
					SqlCmd.Parameters.Clear();
				}
			}
			return dataSet;
		}
		/// <summary>
		/// Executes the requested stored procedure returning a DataSet.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		/// <param name="spReturnValue">The return value from the stored procedure</param>
		/// <returns>DataSet</returns>
		public DataSet ExecDataSet(string procedureName, ref int spReturnValue)
		{
			var adapter = new SqlDataAdapter();
			var dataSet = new DataSet();
			dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture;
			AddReturnValue();
			using (var sqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					sqlConn.Open();
					this.SqlCmd.CommandType = CommandType.StoredProcedure;
					this.SqlCmd.CommandText = procedureName;
					this.SqlCmd.Connection = sqlConn;
					adapter.SelectCommand = this.SqlCmd;
					adapter.Fill(dataSet);
					spReturnValue = GetReturnValue();
					GetOutParameters();
				}
				finally
				{
					this.SqlCmd.Parameters.Clear();
				}
			}
			return dataSet;
		}
		/// <summary>
		/// Executes the requested stored procedure returning a DataTable.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		/// <returns>DataTable</returns>
		public DataTable ExecDataTable(string procedureName)
		{
			return ExecDataSet(procedureName).Tables[0];
		}
		/// <summary>
		/// Executes the requested stored procedure returning a DataTable.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		/// <param name="spReturnValue">The return value from the stored procedure</param>
		/// <returns>DataTable</returns>
		public DataTable ExecDataTable(string procedureName, ref int spReturnValue)
		{
			return ExecDataSet(procedureName, ref spReturnValue).Tables[0];
		}
		/// <summary>
		/// Executes the SQL Statment returning a DataSet.
		/// </summary>
		/// <param name="SQLStatement">The SQL Statment to execute</param>
		/// <returns>DataSet</returns>
		public DataSet ExecDataSetSQL(string SQLStatement)
		{
			SqlDataAdapter adapter = new SqlDataAdapter();
			DataSet dataSet = new DataSet();
			using (SqlConnection SqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					SqlConn.Open();
					SqlCmd.CommandType = CommandType.Text;
					SqlCmd.CommandText = SQLStatement;
					SqlCmd.Connection = SqlConn;
					adapter.SelectCommand = SqlCmd;
					adapter.Fill(dataSet);
					return dataSet;
				}
				finally
				{
					SqlCmd.Parameters.Clear();
				}
			}
		}

		/// <summary>
		/// Executes the requested stored procedure.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		public void ExecNonQuery(string procedureName)
		{
			using (SqlConnection SqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					SqlConn.Open();
					SqlCmd.CommandType = CommandType.StoredProcedure;
					SqlCmd.CommandText = procedureName;
					SqlCmd.Connection = SqlConn;
					SqlCmd.ExecuteNonQuery();
					GetOutParameters();
				}
				finally
				{
					SqlCmd.Parameters.Clear();
				}
			}
		}
		/// <summary>
		/// Executes the requested stored procedure.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		/// <param name="spReturnValue">The return value from the stored procedure</param>
		public void ExecNonQuery(string procedureName, ref int spReturnValue)
		{
			AddReturnValue();
			using (SqlConnection SqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					SqlConn.Open();
					SqlCmd.CommandType = CommandType.StoredProcedure;
					SqlCmd.CommandText = procedureName;
					SqlCmd.Connection = SqlConn;
					SqlCmd.ExecuteNonQuery();
					spReturnValue = GetReturnValue();
					GetOutParameters();
				}
				finally
				{
					SqlCmd.Parameters.Clear();
				}
			}
		}
		/// <summary>
		/// Executes the SQL Statment.
		/// </summary>
		/// <param name="SQLStatement">The SQL Statment to execute</param>
		public void ExecNonQuerySQL(string SQLStatement)
		{
			using (SqlConnection SqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					SqlConn.Open();
					SqlCmd.Parameters.Clear();
					SqlCmd.CommandType = CommandType.Text;
					SqlCmd.CommandText = SQLStatement;
					SqlCmd.Connection = SqlConn;
					SqlCmd.ExecuteNonQuery();
					GetOutParameters();
				}
				finally
				{
					SqlCmd.Parameters.Clear();
				}
			}
		}

		/// <summary>
		/// Executes the requested stored procedure.
		/// </summary>
		/// <param name="procedureName">Sql Stored Procedure Name</param>
		/// <returns>A string containing the value from the first column of the 
		/// first row from the query results.</returns>
		public object ExecScalar(string procedureName)
		{
			object ret = null;
			using (SqlConnection SqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					SqlConn.Open();
					SqlCmd.CommandType = CommandType.StoredProcedure;
					SqlCmd.CommandText = procedureName;
					SqlCmd.Connection = SqlConn;
					ret = SqlCmd.ExecuteScalar();
					GetOutParameters();
				}
				finally
				{
					SqlCmd.Parameters.Clear();
				}
			}
			return ret;
		}
		/// <summary>
		/// Executes the SQL Statment return the first column value of the first row.
		/// </summary>
		/// <param name="SQLStatement">The SQL Statment to execute</param>
		/// <returns>A string containing the value from the first column of the 
		/// first row from the query results.</returns>
		public object ExecScalarSQL(string SQLStatement)
		{
			object ret = null;
			using (SqlConnection SqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					SqlConn.Open();
					SqlCmd.Parameters.Clear();
					SqlCmd.CommandType = CommandType.Text;
					SqlCmd.CommandText = SQLStatement;
					SqlCmd.Connection = SqlConn;
					ret = SqlCmd.ExecuteScalar();
					GetOutParameters();
				}
				finally
				{
					SqlCmd.Parameters.Clear();
				}
			}
			return ret;
		}

		/// <summary>
		/// Derives the Parameters from an Sql Stored Procedure.
		/// </summary>
		/// <param name="spName">Sql Stored Procedure Name</param>
		/// <returns>Array of SqlParameter Objects</returns>
		public SqlParameter[] GetProcParameters(string spName)
		{
			SqlCommand cmd = new SqlCommand(spName);
			SqlParameter[] SpParams;
			using (SqlConnection SqlConn = new SqlConnection(this.GetConnectionString()))
			{
				try
				{
					SqlConn.Open();
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Connection = SqlConn;
					SqlCommandBuilder.DeriveParameters(cmd);

					//Remove Return Value Parameter
					cmd.Parameters.RemoveAt(0);

					SpParams = new SqlParameter[cmd.Parameters.Count];
					cmd.Parameters.CopyTo(SpParams, 0);
				}
				finally
				{
					if (null != cmd)
					{
						cmd.Parameters.Clear();
						cmd.Dispose();
						cmd = null;
					}
				}
			}
			return SpParams;
		}

		/// <summary>
		/// Add multiple parameters to the command object
		/// </summary>
		/// <param name="procedureName">The name of the Stored Procedure</param>
		/// <param name="sourceParameters">An array of Object to used as the values for the stored procedure parameters</param>
		public void AddParams(string procedureName, object[] sourceParameters)
		{
			AddParams(procedureName, sourceParameters, false);
		}
		/// <summary>
		/// Add multiple parameters to the command object
		/// </summary>
		/// <param name="procedureName">The name of the Stored Procedure</param>
		/// <param name="sourceParameters">An array of Object to used as the values for the stored procedure parameters</param>
		/// <param name="includeReturnValueParameter">Indicator to include the return value parameter. Typically false.</param>
		public void AddParams(string procedureName, object[] sourceParameters, bool includeReturnValueParameter)
		{
			if (string.IsNullOrEmpty(this.ConnectionString))
				throw new InvalidOperationException("The Connection is not set.");

			// Pull the parameters for this stored procedure from 
			// the parameter cache (or discover them & populate the cache)
			SqlParameter[] commandParameters =
				SqlParameterCache.GetSpParameterSet(this.ConnectionString, procedureName.ToLower(), includeReturnValueParameter);
			// Assign the provided values
			AssignParameterValues(commandParameters, sourceParameters, procedureName);
		}

		/// <summary>
		/// Add a parameter to the command object
		/// </summary>
		/// <param name="name">Parameter name in the stored procedure</param>
		/// <param name="value">Value to assign to the parameter</param>
		/// <param name="dbType">The SqlDbType of the parameter</param>
		/// <param name="size">The size of the parameter</param>
		/// <param name="direction">The Direction of the parameter</param>
		/// <param name="precision">The Precision of the parameter</param>
		/// <param name="scale">The Scale of the parameter</param>
		public void AddParam(string name, object value, SqlDbType dbType, int size, ParameterDirection direction, byte precision, byte scale)
		{
			if (SqlCmd == null) SqlCmd = new SqlCommand();

			SqlParameter p = SqlCmd.CreateParameter();
			p.Value = value;
			p.SqlDbType = dbType;
			p.Size = size;
			p.Direction = direction;
			if (!name.StartsWith("@")) name = "@" + name;
			p.ParameterName = name;
			p.Scale = scale;
			p.Precision = precision;

			SqlCmd.Parameters.Add(p);
		}
		/// <summary>
		/// Add a parameter to the command object
		/// </summary>
		/// <param name="name">Parameter name in the stored procedure</param>
		/// <param name="value">Value to assign to the parameter</param>
		/// <param name="dbType">The SqlDbType of the parameter</param>
		/// <param name="size">The size of the parameter</param>
		/// <param name="direction">The Direction of the parameter</param>
		public void AddParam(string name, object value, SqlDbType dbType, int size, ParameterDirection direction)
		{
			if (SqlCmd == null) SqlCmd = new SqlCommand();

			SqlParameter p = SqlCmd.CreateParameter();
			p.Value = value;
			p.SqlDbType = dbType;
			p.Size = size;
			p.Direction = direction;
			if (!name.StartsWith("@")) name = "@" + name;
			p.ParameterName = name;

			SqlCmd.Parameters.Add(p);
		}
		/// <summary>
		/// Add a parameter to the command object
		/// </summary>
		/// <param name="name">Parameter name in the stored procedure</param>
		/// <param name="value">Value to assign to the parameter</param>
		/// <param name="dbType">The SqlDbType of the parameter</param>
		/// <param name="size">The size of the parameter</param>
		public void AddParam(string name, object value, SqlDbType dbType, int size)
		{
			if (SqlCmd == null) SqlCmd = new SqlCommand();

			SqlParameter p = SqlCmd.CreateParameter();
			p.Value = value;
			p.SqlDbType = dbType;
			p.Size = size;
			if (!name.StartsWith("@")) name = "@" + name;
			p.ParameterName = name;

			SqlCmd.Parameters.Add(p);
		}
		/// <summary>
		/// Add a parameter to the command object
		/// </summary>
		/// <param name="name">Parameter name in the stored procedure</param>
		/// <param name="value">Value to assign to the parameter</param>
		/// <param name="dbType">The SqlDbType of the parameter</param>
		public void AddParam(string name, object value, SqlDbType dbType)
		{
			if (SqlCmd == null) SqlCmd = new SqlCommand();

			SqlParameter p = SqlCmd.CreateParameter();
			p.Value = value;
			p.SqlDbType = dbType;
			p.Size = 0;
			p.Direction = ParameterDirection.Input;
			if (!name.StartsWith("@")) name = "@" + name;
			p.ParameterName = name;

			SqlCmd.Parameters.Add(p);
		}
		/// <summary>
		/// Adds a Parameter to the command object with it's value set to DBNull then sets it's direction
		/// </summary>
		/// <param name="name">Parameter name in the stored procedure</param>
		/// <param name="direction">The direction of the Parameter (In, In-Out or Out</param>
		public void AddParam(string name, ParameterDirection direction)
		{
			AddWithValue(name, DBNull.Value, direction);
		}
		/// <summary>
		/// Add a Parameter the command object along with its value
		/// </summary>
		/// <param name="name">Parameter name in the stored procedure</param>
		/// <param name="value">Value to assign to the parameter</param>
		/// <param name="direction">The direction of the Parameter (In, In-Out or Out</param>
		/// <remarks>
		/// This uses the new <c>AddWithValue</c> method of the <see cref="SqlParameterCollection"/> object
		/// </remarks>
		public void AddWithValue(string name, object value, ParameterDirection direction)
		{
			if (SqlCmd == null) SqlCmd = new SqlCommand();
			if (!name.StartsWith("@")) name = "@" + name;
			SqlCmd.Parameters.AddWithValue(name, value).Direction = direction;
		}
		/// <summary>
		/// Add a Parameter the command object along with its value
		/// </summary>
		/// <param name="name">Parameter name in the stored procedure</param>
		/// <param name="value">Value to assign to the parameter</param>
		/// <remarks>
		/// This uses the new <c>AddWithValue</c> method of the <see cref="SqlParameterCollection"/> object
		/// </remarks>
		public void AddWithValue(string name, object value)
		{
			if (SqlCmd == null) SqlCmd = new SqlCommand();
			if (!name.StartsWith("@")) name = "@" + name;
			SqlCmd.Parameters.AddWithValue(name, value);
		}
		/// <summary>
		/// Add the return value parameter to the command parameters collection.
		/// </summary>
		public void AddReturnValue()
		{
			if (this.SqlCmd == null) this.SqlCmd = new SqlCommand();
			AddReturnValue(this.SqlCmd);
		}

		/// <summary>
		/// Clears the parameters collection
		/// </summary>
		public void ResetCommand()
		{
			if (SqlCmd != null) SqlCmd.Parameters.Clear();
		}

		/// <summary>
		/// Test the connection to the DB Server using the existing Connection string
		/// </summary>
		/// <returns>True, if a connection can be opened; else False</returns>
		public bool IsConnectionValid(out string ErrorString)
		{
			ErrorString = string.Empty;

			if (string.IsNullOrEmpty(this.ConnectionString))
			{
				ErrorString = "Connection not set.";
				return false;
			}
			using (SqlConnection conn = new SqlConnection(this.ConnectionString))
			{
				try
				{
					conn.Open();
					conn.Close();
					return true;
				}
				catch (Exception ex)
				{
					ErrorString = ex.Message;
					Console.WriteLine(ex.ToString());
				}
			}
			return false;
		}

		#endregion

		#region Helper Functions
		/// <summary>
		/// Gets the Connection String validating of null or empty
		/// </summary>
		/// <returns>The connection string</returns>
		private string GetConnectionString()
		{
			if (null == this.Connection)
				throw new NullReferenceException("Connection not set.");

			return this.Connection.ConnectionString;
		}
		/// <summary>
		/// Extract the Output Parameters
		/// </summary>
		private void GetOutParameters()
		{
			try { _sqlOutParameters = GetOutParameters(this.SqlCmd); }
			catch { }
		}
		/// <summary>
		/// Gets the collection of output parameters if any.
		/// </summary>
		/// <param name="command">The SqlCommand to extract the parameters from.</param>
		/// <returns>A list of SqlParameters objects that are in/out or out parameters.</returns>
		public static List<SqlParameter> GetOutParameters(SqlCommand command)
		{
			Guard.ArgumentNotNull(command, "command");
			var outParams = new List<SqlParameter>();
			try
			{
				foreach (SqlParameter p in command.Parameters)
				{
					if (p.Direction == ParameterDirection.InputOutput
						|| p.Direction == ParameterDirection.Output)
					{
						outParams.Add(p);
					}
				}
			}
			catch { }
			return outParams;
		}
		/// <summary>
		/// Add the RETURN_VALUE Parameter to the provided <see cref="SqlCommand"/>, checking to ensure it doesn't already exist.
		/// </summary>
		/// <param name="command">The <see cref="SqlCommand"/> to add the RETURN_VALUE Parameter to.</param>
		static void AddReturnValue(SqlCommand command)
		{
			Guard.ArgumentNotNull(command, "command");
			if (!command.Parameters.Contains("RETURN_VALUE"))
			{
				foreach (SqlParameter prm in command.Parameters)
				{
					if (prm.Direction == ParameterDirection.ReturnValue)
						command.Parameters.Remove(prm);
				}
				command.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
			}
		}
		/// <summary>
		/// Gets the RETURN_VALUE from the current <see cref="SqlCommand"/>
		/// </summary>
		/// <returns>The stored procedure's Return Value</returns>
		private int GetReturnValue()
		{
			if (null == this.SqlCmd)
				return 0;
			return GetReturnValue(this.SqlCmd);
		}
		/// <summary>
		/// Gets the RETURN_VALUE from the provided <see cref="SqlCommand"/>
		/// </summary>
		/// <param name="command">The <see cref="SqlCommand"/> to retrieve the RETURN_VALUE from.</param>
		/// <returns>The stored procedure's Return Value</returns>
		/// <remarks>
		/// <para>The expected ReturnValue parameter name is 'RETURN_VALUE'. This is the name used by default when using the AddReturnValue method.</para>
		/// </remarks>
		public static int GetReturnValue(SqlCommand command)
		{
			Guard.ArgumentNotNull(command, "command");
			int ret = 0;
			try
			{
				var index = command.Parameters.IndexOf("RETURN_VALUE");
				if (index < 0)
					index = command.Parameters.IndexOf("@RETURN_VALUE");
				if (index < 0)
					return 0;
				var param = command.Parameters[index];
				if (null != param && null != param.Value)
				{
					var objValue = param.Value;
					ret = (int)objValue;
				}
			}
			catch { }
			return ret;
		}
		/// <summary>
		/// This method assigns an array of values to an array of SqlParameters.
		/// </summary>
		/// <param name="commandParameters">Array of SqlParameters to be assigned values</param>
		/// <param name="parameterValues">Array of Parameters holding the values to be assigned</param>
		/// <param name="procedureName">The name of the Stored Procedure</param>
		private void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues, string procedureName)
		{
			#region Validate
			if ((commandParameters == null) || (parameterValues == null))
			{
				//Do nothing if we get no data
				return;
			}
			if (commandParameters.Length != parameterValues.Length)
				throw new ArgumentException("The number of parameters for Stored Procedure [" + procedureName + "] does not match the number of Values you are attempting to add to the Parameter Collection.", "parameterValues");
			#endregion

			#region Assign Values
			//Iterate through the SqlParameters, assigning the values from the SourceParameters array
			for (int i = 0, j = commandParameters.Length; i < j; i++)
			{
				this.AddParam(commandParameters[i].ParameterName, parameterValues[i], commandParameters[i].SqlDbType, commandParameters[i].Size, commandParameters[i].Direction, commandParameters[i].Precision, commandParameters[i].Scale);
			}
			#endregion
		}
		#endregion

		#region IDisposable Members
		/// <summary>
		/// Disposes the Object
		/// </summary>
		public void Dispose()
		{
			Dispose(true);
			// This object will be cleaned up by the Dispose method.
			// Therefore, you should call GC.SupressFinalize to
			// take this object off the finalization queue 
			// and prevent finalization code for this object
			// from executing a second time.
			GC.SuppressFinalize(this);
		}
		// Dispose(bool disposing) executes in two distinct scenarios.
		// If disposing equals true, the method has been called directly
		// or indirectly by a user's code. Managed and unmanaged resources
		// can be disposed.
		// If disposing equals false, the method has been called by the 
		// runtime from inside the finalizer and you SHOULD NOT reference 
		// other objects. Only unmanaged resources can be disposed.
		private void Dispose(bool disposing)
		{
			if (!this.disposed)
			{
				if (disposing)
				{
					if (null != SqlCmd)
					{
						try { SqlCmd.Cancel(); }
						catch { }
						try { SqlCmd.Dispose(); }
						catch { }
						SqlCmd = null;
					}

					if (null != sdrConn)
					{
						try { sdrConn.Dispose(); }
						catch { }
						sdrConn = null;
					}

					disposed = true;

				}
			}
		}
		#endregion

	}
	#endregion
}